﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

namespace HeathCareSystem
{
    public class BenhNhanDAO
    {
        DataProvider dp = new DataProvider();

        //them benh nhan moi
        public bool ThemBenhNhanMoi(BenhNhanDTO benhNhan)
        {
            string strSQL = string.Format("INSERT INTO BenhNhan (MaBenhNhan, HoTen, GioiTinh, NgaySinh, DiaChi, SoDienThoai, QueQuan, TinhTrangHonNhan, MaTaiKhoan)" +
                                            "VALUES (N'{0}', '{1}', N'{2}', CONVERT(DATETIME, '{3}', 102), N'{4}', N'{5}', N'{6}', N'{7}', N'{8}')",
                                            
                                            benhNhan.maBenhNhan, benhNhan.hoTen, benhNhan.gioiTinh, benhNhan.ngaySinh, benhNhan.diaChi, benhNhan.soDienThoai, benhNhan.queQuan, benhNhan.tinhTrangHonNhan, benhNhan.maTaiKhoan);
            return dp.ExecuteNonQuery(strSQL);
        }

        // cap nhat lai thong tin benh nhan
        public bool CapNhatBenhNhan(BenhNhanDTO benhNhan)
        {
            string strSQL = string.Format("UPDATE BenhNhan SET " +
                                          "  HoTen = N'{0}', GioiTinh = N'{1}', NgaySinh = CONVERT(DATETIME, '{2}', 102), " +
                                          "DiaChi = N'{3}', SoDienThoai = N'{4}', QueQuan = N'{5}', TinhTrangHonNhan = N'{6}'" +
                                          " WHERE maBenhNhan = N'{7}'",
                                            benhNhan.hoTen, benhNhan.gioiTinh, benhNhan.ngaySinh, benhNhan.diaChi, benhNhan.soDienThoai, benhNhan.queQuan, benhNhan.tinhTrangHonNhan, benhNhan.maBenhNhan);
            return dp.ExecuteNonQuery(strSQL);
        }

        //tao ma benh nhan tu dong
        public String TaoMaBenhNhan()
        {
            // Chuỗi cố định trong mã phiếu khám bệnh
            string strChu;
            //tao ngay
            DateTime dtNow = DateTime.Now;
            string strDay = dtNow.Day.ToString();
            string strMonth = dtNow.Month.ToString();
            
            string temp = dtNow.Year.ToString();
            string strYear = temp.Substring(2, 2);
            strChu = strYear + strMonth + strDay;
            String strSo = "0";

            String strMaDau = "BN" + strChu + "00";

            String strSql = "SELECT TOP 1 MaBenhNhan FROM BENHNHAN ORDER BY MaBenhNhan DESC";

            try
            {
                String strMaCuoi = dp.ExecuteScalar(strSql).ToString();
                String strTemp = ((int.Parse(strMaCuoi.Remove(0, 8)) + 1)).ToString();
                String strMaBenhNhan = "BN" + strChu;
                while (strMaBenhNhan.Length + strTemp.Length < 10)
                    strMaBenhNhan += strSo;
                return strMaBenhNhan + strTemp;

            }
            catch (Exception)
            {
                return strMaDau;
            }
        }

        // hien thi danh sach benh nhan
        public DataTable DanhSachBenhNhan()
        {
            DataTable dtKetQua = new DataTable();
            string strSQL = string.Format("Select * from BENHNHAN");
            dtKetQua = dp.ExecuteQuery(strSQL);
            return dtKetQua;
        }

        //tim benh nhan theo Ma Benh Nhan
        public BenhNhanDTO TimBenhNhan(string maBenhNhan)
        {
            BenhNhanDTO benhNhanKQ = new BenhNhanDTO();
            DataTable dt = new DataTable();
            string strSQL = string.Format("Select * from BENHNHAN where (MaBenhNhan = N'{0}')", maBenhNhan);
            dt = dp.ExecuteQuery(strSQL);
            try
            {
                benhNhanKQ.maBenhNhan = dt.Rows[0][0].ToString();
                benhNhanKQ.hoTen = dt.Rows[0][1].ToString();
                benhNhanKQ.gioiTinh = dt.Rows[0][2].ToString();
                benhNhanKQ.ngaySinh = DateTime.Parse(dt.Rows[0][3].ToString());
                benhNhanKQ.diaChi = dt.Rows[0][4].ToString();
                benhNhanKQ.soDienThoai = dt.Rows[0][5].ToString();
                benhNhanKQ.queQuan = dt.Rows[0][6].ToString();
                benhNhanKQ.tinhTrangHonNhan = dt.Rows[0][7].ToString();
                benhNhanKQ.maTaiKhoan = Int32.Parse(dt.Rows[0][8].ToString());
            }
            catch(Exception ex)
            {
                benhNhanKQ = null;
            }
            return benhNhanKQ;
        }

        public DataTable BaoCaoChiTietBenhNhan(string queQuan, string gioiTinh, string namNhoNhat, string namLonNhat)
        {
            DataTable dt = new DataTable();

            string strSQL = string.Format("SELECT HoTen AS 'Họ Tên', GioiTinh AS 'Giới Tính', NgaySinh AS 'Ngày sinh', QueQuan AS 'Quê Quán', LEFT(CONVERT(date, NgaySinh, 112), 4) - year(getdate()) AS 'Tuổi' FROM BenhNhan WHERE (QueQuan = N'{0}') AND (GioiTinh = N'{1}') AND (NgaySinh BETWEEN '{2}' AND '{3}')", queQuan, gioiTinh, namNhoNhat, namLonNhat);
            dt = dp.ExecuteQuery(strSQL);
            return dt;
        }

    }
}